package com.cm.projectx.service.analysis.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.cm.projectx.common.cache.SystemOrgCache;
import com.cm.projectx.entity.PriceTask;
import com.cm.projectx.entity.ProductPriceReport;
import com.cm.projectx.entity.ProductPriceReportDetail;
import com.cm.projectx.entity.TaskProcessRelation;
import com.cm.projectx.entity.analysis.ReqDto;
import com.cm.projectx.entity.analysis.RespDto;
import com.cm.projectx.entity.analysis.ValueMapPOJO;
import com.cm.projectx.mapper.PriceTaskMapper;
import com.cm.projectx.mapper.ProductPriceReportDetailMapper;
import com.cm.projectx.mapper.ProductPriceReportMapper;
import com.cm.projectx.mapper.TaskProcessRelationMapper;
import com.cm.projectx.response.Result;
import com.cm.projectx.service.analysis.IEggMilkSaltPriceAnalysisService;
import com.cm.projectx.service.analysis.IOilPriceAnalysisService;
import com.cm.projectx.util.PoiHelper;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.swing.text.html.parser.Entity;
import java.text.DecimalFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
@Slf4j
public class OilPriceAnalysisServiceImpl implements IOilPriceAnalysisService {

    @Resource
    private PriceTaskMapper priceTaskMapper;

    @Resource
    private TaskProcessRelationMapper taskProcessRelationMapper;

    @Resource
    private ProductPriceReportMapper productPriceReportMapper;

    @Resource
    private ProductPriceReportDetailMapper productPriceReportDetailMapper;

    private static final DecimalFormat df = new DecimalFormat("0.00");

    @Data
    static class CacheData {
        private List<TaskProcessRelation> taskProcessRelations;
        private List<ProductPriceReport> productPriceReports;
        //食用油中的收购价格
        private List<ProductPriceReportDetail> productPriceReportDetails;
        //食用油中的出厂价格
        private List<ProductPriceReportDetail> productPrice1ReportDetails;
    }

    private CacheData getData(int taskId, String reportDate, String productName) {
        CacheData cacheData = new CacheData();

        LambdaQueryWrapper<TaskProcessRelation> taskProcessRelationLambdaQueryWrapper = new LambdaQueryWrapper<>();
        taskProcessRelationLambdaQueryWrapper.eq(TaskProcessRelation::getTaskId, taskId);
        taskProcessRelationLambdaQueryWrapper.eq(TaskProcessRelation::getStartTime, reportDate);
        List<TaskProcessRelation> taskProcessRelations = taskProcessRelationMapper.selectList(taskProcessRelationLambdaQueryWrapper);
        cacheData.setTaskProcessRelations(taskProcessRelations);
        if (CollectionUtils.isEmpty(taskProcessRelations)) {
            return cacheData;
        }

        List<String> processIds = taskProcessRelations.stream().map(TaskProcessRelation::getProcessId).collect(Collectors.toList());
        LambdaQueryWrapper<ProductPriceReport> productPriceReportLambdaQueryWrapper = new LambdaQueryWrapper<>();
        productPriceReportLambdaQueryWrapper.in(ProductPriceReport::getProcessId, processIds);
        List<ProductPriceReport> productPriceReports = productPriceReportMapper.selectList(productPriceReportLambdaQueryWrapper);
        cacheData.setProductPriceReports(productPriceReports);
        if (CollectionUtils.isEmpty(productPriceReports)) {
            return cacheData;
        }

        List<String> parentIds = productPriceReports.stream().map(ProductPriceReport::getId).collect(Collectors.toList());
        LambdaQueryWrapper<ProductPriceReportDetail> productPriceReportDetailLambdaQueryWrapper = new LambdaQueryWrapper<>();
        productPriceReportDetailLambdaQueryWrapper.in(ProductPriceReportDetail::getParentId, parentIds);
        productPriceReportDetailLambdaQueryWrapper.eq(ProductPriceReportDetail::getMetricType, "收购价格");
        productPriceReportDetailLambdaQueryWrapper.like(StringUtils.hasText(productName), ProductPriceReportDetail::getName, productName);
        List<ProductPriceReportDetail> productPriceReportDetails = productPriceReportDetailMapper.selectList(productPriceReportDetailLambdaQueryWrapper);
        cacheData.setProductPriceReportDetails(productPriceReportDetails);

        productPriceReportDetailLambdaQueryWrapper.clear();
        productPriceReportDetailLambdaQueryWrapper.in(ProductPriceReportDetail::getParentId, parentIds);
        productPriceReportDetailLambdaQueryWrapper.eq(ProductPriceReportDetail::getMetricType, "出厂价格");
        productPriceReportDetailLambdaQueryWrapper.like(StringUtils.hasText(productName), ProductPriceReportDetail::getName, productName);
        List<ProductPriceReportDetail> productPriceReportDetails1 = productPriceReportDetailMapper.selectList(productPriceReportDetailLambdaQueryWrapper);
        cacheData.setProductPrice1ReportDetails(productPriceReportDetails1);
        return cacheData;
    }

    private List<RespDto> generateData(CacheData nowDate, CacheData lastDate) {
        //根据当前数据生成商品集合
        Map<String, RespDto> nowMap = new HashMap<>();
        //缓存当期商品价格
        Map<String, List<Double>> nowPriceMap = new HashMap<>();
        Map<String, ProductPriceReport> productPriceReportMap = nowDate.getProductPriceReports().stream().collect(Collectors.toMap(ProductPriceReport::getId, Function.identity()));
        List<ProductPriceReportDetail> nowProductPriceReportDetails = nowDate.getProductPriceReportDetails();
        for (ProductPriceReportDetail detail : nowProductPriceReportDetails) {
            String productName = detail.getName();
            RespDto respDto = nowMap.get(productName);
            if (null == respDto) {
                respDto = new RespDto();
                respDto.setProductName(productName);
                respDto.setProductLevel(detail.getKind());
                respDto.setProductUnit(detail.getUnit());
                respDto.setPriceType(detail.getMetricType());
                respDto.setFloatValue(detail.getFloatValue());
                respDto.setCircleValues(new ArrayList<>());
                respDto.setLastMetrics(new ArrayList<>());
                respDto.setNowMetrics(new ArrayList<>());
                respDto.setMonitorPriceMapList(new HashMap<>());
                nowMap.put(productName, respDto);
            }

            //记录监测点
            ProductPriceReport productPriceReport = productPriceReportMap.get(detail.getParentId());
            if (null != productPriceReport) {
                ValueMapPOJO valueMapPOJO = new ValueMapPOJO();
                valueMapPOJO.setName("收购价格");
                valueMapPOJO.setValue(detail.getPrice() + "");
                if(respDto.getMonitorPriceMapList().containsKey(productPriceReport.getMonitorPoint())){
                    respDto.getMonitorPriceMapList().get(productPriceReport.getMonitorPoint()).add(valueMapPOJO);
                }else{
                    respDto.getMonitorPriceMapList().put(productPriceReport.getMonitorPoint(),new ArrayList<ValueMapPOJO>(){{
                        add(valueMapPOJO);
                    }});
                }
            }
            //缓存价格
            List<Double> priceList = nowPriceMap.computeIfAbsent(productName + "收购价格", key -> new ArrayList<>());
            priceList.add(detail.getPrice());
        }

        //缓存上期商品价格
        Map<String, List<Double>> lastPriceMap = new HashMap<>();
        List<ProductPriceReportDetail> lastProductPriceReportDetails = lastDate.getProductPriceReportDetails();
        for (ProductPriceReportDetail detail : lastProductPriceReportDetails) {
            RespDto respDto = nowMap.get(detail.getName());
            if (null != respDto) {
                //缓存价格
                List<Double> priceList = lastPriceMap.computeIfAbsent(detail.getName() + "收购价格", key -> new ArrayList<>());
                priceList.add(detail.getPrice());
            }
        }
        List<RespDto> list = new ArrayList<>();
        //计算数值
        for (Map.Entry<String, RespDto> entry : nowMap.entrySet()) {
            String productName = entry.getKey();
            RespDto respDto = entry.getValue();
            //计算当期平均值
            ValueMapPOJO nowAvgPOJO = new ValueMapPOJO();
            double nowAvg = 0;
            nowAvgPOJO.setName("收购价格(平均价)");
            List<Double> nowPriceList = nowPriceMap.get(productName + "收购价格");
            if (CollectionUtils.isNotEmpty(nowPriceList)) {
                nowAvg = nowPriceList.stream().filter(Objects::nonNull).mapToDouble(Double::doubleValue).average().orElse(0);
            }
            nowAvgPOJO.setValue(df.format(nowAvg));
            respDto.getNowMetrics().add(nowAvgPOJO);

            //计算上期平均值
            ValueMapPOJO lastAvgPOJO = new ValueMapPOJO();
            double lastAvg = 0;
            lastAvgPOJO.setName("收购价格(平均价)");
            List<Double> lastPriceList = lastPriceMap.get(productName + "收购价格");
            if (CollectionUtils.isNotEmpty(lastPriceList)) {
                lastAvg = lastPriceList.stream().filter(Objects::nonNull).mapToDouble(Double::doubleValue).average().orElse(0);
            }
            lastAvgPOJO.setValue(df.format(lastAvg));
            respDto.getLastMetrics().add(lastAvgPOJO);

            //计算环比
            ValueMapPOJO radioPOJO = new ValueMapPOJO();
            radioPOJO.setName("环比(收购价格)");
            double ratio;
            if (nowAvg == 0) {
                ratio = 0;
            } else if (lastAvg == 0) {
                ratio = 100;
            } else {
                ratio = (nowAvg - lastAvg) / lastAvg * 100;
            }
            radioPOJO.setValue(df.format(ratio));
            respDto.getCircleValues().add(radioPOJO);

            list.add(respDto);
        }

        //计算完收购价格后 继续计算出厂价格
        Map<String, List<Double>> nowPrice1Map = new HashMap<>();
        List<ProductPriceReportDetail> nowProductPrice1ReportDetails = nowDate.getProductPrice1ReportDetails();
        for (ProductPriceReportDetail detail1 : nowProductPrice1ReportDetails) {
            String productName = detail1.getName();
            RespDto respDto = nowMap.get(productName);
            if (null == respDto) {
                respDto = new RespDto();
                respDto.setProductName(productName);
                respDto.setProductLevel(detail1.getKind());
                respDto.setProductUnit(detail1.getUnit());
                respDto.setPriceType(detail1.getType());
                respDto.setFloatValue(detail1.getFloatValue());
                respDto.setCircleValues(new ArrayList<>());
                respDto.setLastMetrics(new ArrayList<>());
                respDto.setNowMetrics(new ArrayList<>());
                respDto.setMonitorPriceMapList(new HashMap<>());
                nowMap.put(productName, respDto);
                list.add(respDto);
            }
            //记录监测点
            ProductPriceReport productPriceReport = productPriceReportMap.get(detail1.getParentId());
            if (null != productPriceReport) {
                ValueMapPOJO valueMapPOJO = new ValueMapPOJO();
                valueMapPOJO.setName("出厂价格");
                valueMapPOJO.setValue(detail1.getPrice() + "");
                if(respDto.getMonitorPriceMapList().containsKey(productPriceReport.getMonitorPoint())){
                    respDto.getMonitorPriceMapList().get(productPriceReport.getMonitorPoint()).add(valueMapPOJO);
                }else{
                    respDto.getMonitorPriceMapList().put(productPriceReport.getMonitorPoint(),new ArrayList<ValueMapPOJO>(){{
                        add(valueMapPOJO);
                    }});
                }
            }

            //缓存价格
            List<Double> priceList = nowPriceMap.computeIfAbsent(productName + "出厂价格", key -> new ArrayList<>());
            priceList.add(detail1.getPrice());
        }
        List<ProductPriceReportDetail> lastProductPrice1ReportDetails = lastDate.getProductPrice1ReportDetails();
        for (ProductPriceReportDetail detail : lastProductPrice1ReportDetails) {
            RespDto respDto = nowMap.get(detail.getName());
            if (null != respDto) {
                //缓存价格
                List<Double> priceList = lastPriceMap.computeIfAbsent(detail.getName() + "出厂价格", key -> new ArrayList<>());
                priceList.add(detail.getPrice());
            }
        }

        //计算数值
        for (Map.Entry<String, RespDto> entry : nowMap.entrySet()) {
            String productName = entry.getKey();
            RespDto respDto = entry.getValue();
            //计算当期平均值
            ValueMapPOJO nowAvgPOJO = new ValueMapPOJO();
            double nowAvg = 0;
            nowAvgPOJO.setName("出厂价格(平均价)");
            List<Double> nowPriceList = nowPriceMap.get(productName + "出厂价格");
            if (CollectionUtils.isNotEmpty(nowPriceList)) {
                nowAvg = nowPriceList.stream().filter(Objects::nonNull).mapToDouble(Double::doubleValue).average().orElse(0);
            }
            nowAvgPOJO.setValue(df.format(nowAvg));
            respDto.getNowMetrics().add(nowAvgPOJO);

            //计算上期平均值
            ValueMapPOJO lastAvgPOJO = new ValueMapPOJO();
            double lastAvg = 0;
            lastAvgPOJO.setName("出厂价格(平均价)");
            List<Double> lastPriceList = lastPriceMap.get(productName + "出厂价格");
            if (CollectionUtils.isNotEmpty(lastPriceList)) {
                lastAvg = lastPriceList.stream().filter(Objects::nonNull).mapToDouble(Double::doubleValue).average().orElse(0);
            }
            lastAvgPOJO.setValue(df.format(lastAvg));
            respDto.getLastMetrics().add(lastAvgPOJO);

            //计算环比
            ValueMapPOJO radioPOJO = new ValueMapPOJO();
            radioPOJO.setName("环比(出厂价格)");
            double ratio;
            if (nowAvg == 0) {
                ratio = 0;
            } else if (lastAvg == 0) {
                ratio = 100;
            } else {
                ratio = (nowAvg - lastAvg) / lastAvg * 100;
            }
            radioPOJO.setValue(df.format(ratio));
            respDto.getCircleValues().add(radioPOJO);

            //list.add(respDto);
        }

        return list;
    }

    /**
     * 输出数据分析
     *
     * @param dto 查询参数
     * @return 数据分析
     */
    @Override
    public Result getPriceAnalysis(ReqDto dto) {
        Result result = new Result();

        int taskId = dto.getTaskId();
        PriceTask priceTask = priceTaskMapper.selectById(taskId);
        if (null == priceTask) {
            result.setCode(-1);
            result.setMsg("任务不存在。");
            return result;
        }

        String nowReportDate = dto.getNowReportDate();
        if (!StringUtils.hasText(nowReportDate)) {
            result.setCode(-1);
            result.setMsg("当前时间不能为空。");
            return result;
        }
        String lastReportDate = StringUtils.hasText(dto.getNowReportDate()) ? dto.getNowReportDate() : nowReportDate;
        CacheData nowData = getData(taskId, nowReportDate, dto.getProductName());
        if (CollectionUtils.isEmpty(nowData.getProductPriceReportDetails())) {
            result.setCode(200);
            result.addData("priceResult", new ArrayList<>());

        } else {
            CacheData lastData = getData(taskId, lastReportDate, dto.getProductName());
            result.setCode(200);
            result.addData("priceResult", generateData(nowData, lastData));
        }
        return result;
    }

    @Override
    public void exportPriceAnalysis(ReqDto dto, HttpServletResponse response, String type) {
        String name = "价格监测报表";
        switch (type) {
            case "oil":
                name = "食用油";
                break;
            default:
                ;
        }
        int taskId = dto.getTaskId();
        PriceTask priceTask = priceTaskMapper.selectById(taskId);
        if (null == priceTask) {
            log.error("任务不存在。");
            return;
        }
        String nowReportDate = dto.getNowReportDate();
        if (!StringUtils.hasText(nowReportDate)) {
            log.error("当前时间不能为空。");
            return;
        }
        String lastReportDate = StringUtils.hasText(dto.getNowReportDate()) ? dto.getNowReportDate() : nowReportDate;
        List<RespDto> list = null;
        CacheData nowData = getData(taskId, nowReportDate, dto.getProductName());
        if (!CollectionUtils.isEmpty(nowData.getProductPriceReportDetails())) {
            CacheData lastData = getData(taskId, lastReportDate, dto.getProductName());
            list = generateData(nowData, lastData);
        }
        Map<String, String> monitorMap = getMonitorMap(nowData);
        XSSFWorkbook workbook = generateExportFile(list, name, monitorMap);
        Map<String, Object> fileInfo = new HashMap<>();
        fileInfo.put("excel_file_name", name + ".xlsx");
        fileInfo.put("excel_file_content", workbook);
        PoiHelper.downFileByBrowser(fileInfo, response);
    }

    private static Map<String, String> getMonitorMap(CacheData nowData){
        List<ProductPriceReportDetail> productPriceReportDetails = nowData.getProductPriceReportDetails();
        List<ProductPriceReport> productPriceReports = nowData.getProductPriceReports();

        Map<String, String> monitorMap = new HashMap<>();
        if(CollectionUtils.isEmpty(productPriceReportDetails)){
            return monitorMap;
        }
        if(CollectionUtils.isEmpty(productPriceReports)){
            return monitorMap;
        }
        Set<String> monitorIds = new HashSet<>();
        Map<String, ProductPriceReport> productPriceReportIdMap = productPriceReports.stream().collect(Collectors.toMap(ProductPriceReport::getId, Function.identity()));
        for(ProductPriceReportDetail detail : productPriceReportDetails){
            String parentId = detail.getParentId();
            ProductPriceReport report = productPriceReportIdMap.get(parentId);
            if(report == null){
                continue;
            }
            String monitorId = report.getMonitorPoint();
            monitorIds.add(monitorId);
        }
        for(String monitorId : monitorIds){
            monitorMap.put(monitorId, SystemOrgCache.getOrgName(monitorId));
        }
        return monitorMap;
    }

    private XSSFWorkbook generateExportFile(List<RespDto> list, String name, Map<String, String> monitorMap) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(name);
        Map<String, Integer> monitorSotrMap = generateExportFileHeader(workbook, monitorMap);
        //填充数据行
        if (CollectionUtils.isEmpty(list)) {
            return workbook;
        }
        int rowNum = 2;
        for (RespDto dto : list) {
            Row row = sheet.createRow(rowNum++);
            int cellNum = 0;
            row.createCell(cellNum++).setCellValue(dto.getProductName() != null ? dto.getProductName() : "");
            row.createCell(cellNum++).setCellValue(dto.getProductLevel() != null ? dto.getProductLevel() : "");
            row.createCell(cellNum++).setCellValue(dto.getProductUnit() != null ? dto.getProductUnit() : "");
            String radio = getMonittorValue(dto.getCircleValues(),"环比(收购价格)");
            row.createCell(cellNum++).setCellValue(radio != null ? radio : "");
            String radio1 = getMonittorValue(dto.getCircleValues(),"环比(出厂价格)");
            row.createCell(cellNum++).setCellValue(radio1 != null ? radio1 : "");

            String lastPrice =getMonittorValue(dto.getLastMetrics(),"收购价格(平均价)");
            row.createCell(cellNum++).setCellValue(lastPrice != null ? lastPrice : "");
            String lastPrice1 =getMonittorValue(dto.getLastMetrics(),"出厂价格(平均价)");
            row.createCell(cellNum++).setCellValue(lastPrice1 != null ? lastPrice1 : "");

            String nowPrice1 =getMonittorValue(dto.getNowMetrics(),"收购价格(平均价)");
            row.createCell(cellNum++).setCellValue(nowPrice1 != null ? nowPrice1 : "");
            String nowPrice2 =getMonittorValue(dto.getNowMetrics(),"出厂价格(平均价)");
            row.createCell(cellNum++).setCellValue(nowPrice2 != null ? nowPrice2 : "");

            HashMap<String, List<ValueMapPOJO>> monitorPricesMap = dto.getMonitorPriceMapList();
            for (Map.Entry<String, Integer> entry : monitorSotrMap.entrySet()) {
                Integer index = entry.getValue();
                String value = "0.0";
                row.createCell(index).setCellValue(value);
                row.createCell(index+1).setCellValue(value);
            }
            for (Map.Entry<String, List<ValueMapPOJO>> monitorPrices : monitorPricesMap.entrySet()) {
                String monitorId = monitorPrices.getKey();
                List<ValueMapPOJO> valueMapPOJOS = monitorPrices.getValue();
                int index = monitorSotrMap.getOrDefault(monitorId, -1);
                if (index > -1) {
                    Cell cell = row.getCell(index);
                    String value = getMonittorValue(valueMapPOJOS,"收购价格");
                    if(org.apache.commons.lang3.StringUtils.isBlank( value)){
                        value = "0.0";
                    }
                    if (cell != null) {
                        cell.setCellValue(value);
                    } else {
                        row.createCell(index).setCellValue(value);
                    }

                    Cell cell1 = row.getCell(index+1);
                    String value1 = getMonittorValue(valueMapPOJOS,"出厂价格");
                    if(org.apache.commons.lang3.StringUtils.isBlank( value)){
                        value1 = "0.0";
                    }
                    if (cell1 != null) {
                        cell1.setCellValue(value1);
                    } else {
                        row.createCell(index+1).setCellValue(value1);
                    }

                }
            }
        }
        return workbook;
    }

    private String getMonittorValue(List<ValueMapPOJO> monitorPrices, String name) {
        for (ValueMapPOJO monitorPrice : monitorPrices) {
            if (monitorPrice.getName().equals(name)) {
                return monitorPrice.getValue();
            }
        }
        return null;
    }

    /**
     * 生成Excel表头，监测点从monitorMap横向展开为列
     *
     * @param workbook   Excel工作簿
     * @param monitorMap 监测点映射（key:监测点标识, value:监测点名称）
     * @return 监测点标识与列索引的映射（用于后续填充数据）
     */
    public static Map<String, Integer> generateExportFileHeader(XSSFWorkbook workbook, Map<String, String> monitorMap) {
        // 创建表头样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        Sheet sheet = workbook.getSheetAt(0);

        // 1. 计算总列数：固定列(9) + 监测点数量×2（每个监测点占2列）
        int monitorCount = CollectionUtils.isEmpty(monitorMap) ? 0 : monitorMap.size();
        int totalColumns = 9 + monitorCount * 2;

        // 2. 合并单元格（核心逻辑）
        // 纵向合并：商品名称、规格等级、计量单位（跨2行）
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); // 商品名称（行0-1，列0）
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); // 规格等级（行0-1，列1）
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); // 计量单位（行0-1，列2）

        // 横向合并：价格相关列（行0跨2列）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4)); // 环比（列3-4）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 6)); // 上期平均价（列5-6）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 8)); // 平均价（列7-8）

        // 3. 监测点横向合并（每个监测点在第0行跨2列）
        int currentCol = 9;
        if (!CollectionUtils.isEmpty(monitorMap)) {
            for (int i = 0; i < monitorCount; i++) {
                // 每个监测点在第0行合并2列（对应第1行的收购价和出厂价）
                sheet.addMergedRegion(new CellRangeAddress(0, 0, currentCol, currentCol + 1));
                currentCol += 2; // 每个监测点占2列
            }
        }

        // 4. 创建表头内容
        // 行0（一级表头）
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellValue("商品名称");
        row0.createCell(1).setCellValue("规格等级");
        row0.createCell(2).setCellValue("计量单位");
        row0.createCell(3).setCellValue("环比");
        row0.createCell(5).setCellValue("上期平均价");
        row0.createCell(7).setCellValue("平均价");

        // 行1（二级表头：价格类型）
        Row row1 = sheet.createRow(1);
        row1.createCell(3).setCellValue("收购价");
        row1.createCell(4).setCellValue("出厂价");
        row1.createCell(5).setCellValue("收购价");
        row1.createCell(6).setCellValue("出厂价");
        row1.createCell(7).setCellValue("收购价");
        row1.createCell(8).setCellValue("出厂价");

        // 5. 处理监测点（第0行显示名称，第1行显示收购价和出厂价）
        currentCol = 9;
        Map<String, Integer> monitorSortMap = new HashMap<>(); // 存储监测点首个列索引
        if (!CollectionUtils.isEmpty(monitorMap)) {
            for (Map.Entry<String, String> entry : monitorMap.entrySet()) {
                // 第0行：监测点名称（合并2列）
                Cell monitorCell = row0.createCell(currentCol);
                monitorCell.setCellValue(entry.getValue());
                monitorCell.setCellStyle(headerStyle);

                // 第1行：收购价和出厂价
                Cell purchaseCell = row1.createCell(currentCol);
                purchaseCell.setCellValue("收购价");
                purchaseCell.setCellStyle(headerStyle);

                Cell factoryCell = row1.createCell(currentCol + 1);
                factoryCell.setCellValue("出厂价");
                factoryCell.setCellStyle(headerStyle);

                // 记录监测点起始列索引（用于后续填充数据）
                monitorSortMap.put(entry.getKey(), currentCol);

                currentCol += 2; // 移动到下一个监测点的起始列
            }
        }

        // 6. 统一设置样式和列宽
        for (int i = 0; i < totalColumns; i++) {
            // 设置行0单元格样式
            if (row0.getCell(i) != null) {
                row0.getCell(i).setCellStyle(headerStyle);
            }
            // 设置行1单元格样式
            if (row1.getCell(i) != null) {
                row1.getCell(i).setCellStyle(headerStyle);
            }
            // 固定列宽
            sheet.setColumnWidth(i, 20 * 256);
        }

        return monitorSortMap;
    }

    /**
     * 创建表头样式
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
}
